package com.database;

import java.sql.SQLException;

import com.config.readConfig;
import com.database.stat.*;
public class StatSql {
	
public String getOperationTotal(String stat,String sqlType,String form,String Branchid,String type,String timeType,String start_date,String end_date,String year,String month,String typeName) throws SQLException{
	String sql="";
	String sql_by="";
	String sql_w="";
	String[] daytime;
	daytime=new String[3];
	String yy="";
	String mm="";
	String dd="";
	if(!start_date.equals("")){
	 daytime=start_date.split("-");
	 yy=daytime[0];
	 mm=daytime[1];
	 dd=daytime[2];
	 start_date=yy+""+mm+""+dd;
	}
	if(!end_date.equals("")){
		 daytime=end_date.split("-");
		 yy=daytime[0];
		 mm=daytime[1];
		 dd=daytime[2];
		 end_date=yy+""+mm+""+dd;
	}
	if(timeType.equals("1")){
		sql_w=" and convert(varchar(40),GetNumberTime,112)>='"+start_date+"' " +
			"and convert(varchar(40),GetNumberTime,112)<='"+end_date+"' ";
	}
	else if(timeType.equals("2")){
		sql_w=" and convert(varchar(40),GetNumberTime,112)='"+start_date+"' " ;
	}
	else if(timeType.equals("3")){
		sql_w=" and convert(varchar(40),GetNumberTime,112)>='"+start_date+"' " +
		"and convert(varchar(40),GetNumberTime-7,112)<='"+start_date+"' ";
	}
	else if(timeType.equals("4")){
		sql_w=" and convert(varchar(40),GetNumberTime,112) like '"+year+month+"%' " ;
	}
	else if(timeType.equals("5")){
		if(month.equals("1")){
			sql_w=" and convert(varchar(40),GetNumberTime,112) >= '"+year+"01"+"' and  convert(varchar(40),GetNumberTime,112)<='"+year+"04"+"'" ;
		}
		if(month.equals("2")){
			sql_w=" and convert(varchar(40),GetNumberTime,112) >= '"+year+"04"+"' and  convert(varchar(40),GetNumberTime,112)<='"+year+"07"+"'" ;
		}
		if(month.equals("3")){
			sql_w=" and convert(varchar(40),GetNumberTime,112) >= '"+year+"07"+"' and  convert(varchar(40),GetNumberTime,112)<='"+year+"10"+"'" ;
		}
		if(month.equals("4")){
			sql_w=" and convert(varchar(40),GetNumberTime,112) >= '"+year+"10"+"' and year(GetNumberTime)='"+year+"'" ;
		}
	}
	else if(timeType.equals("6")){
		if(month.equals("1")){
			sql_w=" and convert(varchar(40),GetNumberTime,112) >= '"+year+"01"+"' and  convert(varchar(40),GetNumberTime,112)<='"+year+"07"+"'" ;
		}
		if(month.equals("2")){
			sql_w=" and convert(varchar(40),GetNumberTime,112) >= '"+year+"07"+"' and year(GetNumberTime)='"+year+"'" ;
		}
	}
	else if(timeType.equals("7")){
		sql_w="  and year(GetNumberTime)='"+year+"'" ;
	}
	 
	if(form.equals("ototal")&&sqlType.equals("1")){
		sql=new OperationTotal().getOtotal(stat,sql_w,Branchid,type,typeName);
	}
	if(form.equals("wtime")&&sqlType.equals("1")){
		sql=new Wtime().getWtime(stat,sql_w,Branchid,type,typeName);
	}
	if(form.equals("satis")&&sqlType.equals("1")){
		sql=new Satis().getSatis(stat,sql_w,Branchid,type,typeName);
	}
	if(form.equals("ttime")&&sqlType.equals("1")){
		sql=new Ttime().getTtime(stat,sql_w,Branchid,type,typeName);
	}
	if(form.equals("period")&&sqlType.equals("1")){
		sql=new Period().getPeriod(stat,sql_w,Branchid,type,typeName);
	}
	
	
	
	if(form.equals("ototal")&&sqlType.equals("2")){
		sql=new StatSql().getOtotalQX("ototal",Branchid,sql_w,typeName);
	}
	if(form.equals("wtime")&&sqlType.equals("2")){
		sql=new StatSql().getOtotalQX("wtime",Branchid,sql_w,typeName);
	}
	if(form.equals("satis")&&sqlType.equals("2")){
		sql=new StatSql().getOtotalQX("statis",Branchid,sql_w,typeName);
	}
	if(form.equals("ttime")&&sqlType.equals("2")){
		sql=new StatSql().getOtotalQX("ttime",Branchid,sql_w,typeName);
	}
	if(form.equals("period")&&sqlType.equals("2")){
		sql=new StatSql().getOtotalQX("period",Branchid,sql_w,typeName);
	}
		return sql;
		}

public String getOtotalQX(String QXtype,String branchid,String sql_w,String typename){
	String sql="";
	int Stime=Integer.parseInt(new readConfig().getPara("db.property","servertime"));
	
	
	if(QXtype.equals("ototal")){
		sql="select  convert(varchar(40),GetNumberTime,112)typex,"+
	"count(1)getNums,"+
	"sum(case when CustomerStatus=4 then 1 else 0 end)servernum, "+
	"sum(case when CustomerStatus=2 then 1 else 0 end)giveupnum  "+
	" from cqms_wintype_record where branchid like '"+branchid+"%'  "+sql_w+
	" group by convert(varchar(40),GetNumberTime,112)";
	}
	if(QXtype.equals("wtime")){
		sql="select convert(varchar(40),GetNumberTime,112)typex, " +
				" sum(case when datediff(minute,GetNumberTime,CallTime)<=3 then 1 else 0 end) three," +
				" sum(case when datediff(minute,GetNumberTime,CallTime)>3 and datediff(minute,GetNumberTime,CallTime)<=5 then 1 else 0 end) threeToFive," +
				" sum(case when datediff(minute,GetNumberTime,CallTime)>5 and datediff(minute,GetNumberTime,CallTime)<=10 then 1 else 0 end) FiveToTen," +
				" sum(case when datediff(minute,GetNumberTime,CallTime)>10 and datediff(minute,GetNumberTime,CallTime)<=20 then 1 else 0 end) tenToTwenty," +
				" sum(case when datediff(minute,GetNumberTime,CallTime)>20 then 1 else 0 end) twenty, avg(datediff(minute,GetNumberTime,CallTime)) avgwaittime" +
				" from cqms_wintype_record  " +
				" where branchid like '"+branchid+"%'  " +
				" and CustomerStatus=4 "+
				" and datediff(s,GetNumberTime,CallTime)>=0 "+
				" and datediff(s,CallTime,servertime)>="+Stime+" "+
						""+sql_w+
				" group by convert(varchar(40),GetNumberTime,112)";
				
	}
	if(QXtype.equals("statis")){
		
		sql="select convert(varchar(40),GetNumberTime,112)typex,count(1) servernum," +
				"isnull(sum(case when EvaluationType<>'0' then 1 else 0 end),0) evalnum," +
				"isnull(sum(case when EvaluationType='04' then 1 else 0 end),0) one," +
				"isnull(sum(case when EvaluationType='03' then 1 else 0 end),0) two," +
				"isnull(sum(case when EvaluationType='02' then 1 else 0 end),0) three," +
				"isnull(sum(case when EvaluationType='01' then 1 else 0 end),0) four," +
				"isnull(sum(case when EvaluationType='0' and askEvalTime is not null  then 1 else 0 end),0) none," +
				"isnull(sum(case when EvaluationType='0' and askEvalTime is  null then 1 else 0 end),0) wq  " +
				"from cqms_wintype_record r,cqms_branch b  " +
				" where b.branchid like '"+branchid+"%'  " +
				" and CustomerStatus=4 "+
				" and datediff(s,GetNumberTime,CallTime)>=0 "+
				" and datediff(s,CallTime,servertime)>="+Stime+" "+
						""+sql_w+
				"  and CustomerStatus='4' " +
				"and r.branchid=b.branchid  " +
				" group by convert(varchar(40),GetNumberTime,112)";
			
				
	}
	if(QXtype.equals("ttime")){
		sql="select convert(varchar(40),GetNumberTime,112)typex,count(1) servernum, " +
		"max(datediff(n,calltime,servertime)) maxservertime, " +
		"min(datediff(n,calltime,servertime)) minservertime, " +
		"avg(datediff(n,calltime,servertime)) avgservertime   " +
		"from cqms_wintype_record where branchid like '"+branchid+"%'  " +
		" and CustomerStatus=4 "+
		" and datediff(s,GetNumberTime,CallTime)>=0 "+
		" and datediff(s,CallTime,servertime)>="+Stime+" "+
				""+sql_w+
		"	and winid is not null  " +
		" group by convert(varchar(40),GetNumberTime,112)";
				
	}
	if(QXtype.equals("period")){
		sql="select count(1)heji,"+
		"convert(varchar(40),GetNumberTime,112)typex,"+
"sum(case when Convert(varchar(30),datepart(hour,r.getNumberTime))=8 then 1 else 0 end) per8,"+
"sum(case when Convert(varchar(30),datepart(hour,r.getNumberTime))=9 then 1 else 0 end) per9,"+
"sum(case when Convert(varchar(30),datepart(hour,r.getNumberTime))=10 then 1 else 0 end) per10,"+
"sum(case when Convert(varchar(30),datepart(hour,r.getNumberTime))=11 then 1 else 0 end) per11,"+
"sum(case when Convert(varchar(30),datepart(hour,r.getNumberTime))=12 then 1 else 0 end) per12,"+
"sum(case when Convert(varchar(30),datepart(hour,r.getNumberTime))=13 then 1 else 0 end) per13,"+
"sum(case when Convert(varchar(30),datepart(hour,r.getNumberTime))=14 then 1 else 0 end) per14,"+
"sum(case when Convert(varchar(30),datepart(hour,r.getNumberTime))=15 then 1 else 0 end) per15,"+
"sum(case when Convert(varchar(30),datepart(hour,r.getNumberTime))=16 then 1 else 0 end) per16,"+
"sum(case when Convert(varchar(30),datepart(hour,r.getNumberTime))=17 then 1 else 0 end) per17,"+
"sum(case when Convert(varchar(30),datepart(hour,r.getNumberTime))=18 then 1 else 0 end) per18,"+
"sum(case when Convert(varchar(30),datepart(hour,r.getNumberTime))>18 or Convert(varchar(30),datepart(hour,r.getNumberTime))<24 then 1 else 0 end) xiao18"+
		" from cqms_wintype_record  r" +
				" where branchid like '"+branchid+"%'  "+sql_w+
				" group by convert(varchar(40),GetNumberTime,112)";
				
	}
return sql;
}


public String getOperationMonitor(String Branchid,String type) throws SQLException{
	String vipList=new VipdjSet().getVipList();
	
	String sql="";
	String sql_by="";
	String sql_w=" and CONVERT(CHAR(10),GetNumberTime,120)= CONVERT(CHAR(10),GetDate(),120)";

	if(type.equals("1")){
		sql="select count(1) getnums,"+"sum(case when CustomerStatus=4 then 1 else 0 end) servernum," +
	 	"sum(case when CustomerStatus=2 then 1 else 0 end) giveupnum, "+
	 	"sum(case when CustomerStatus=0 then 1 else 0 end) waitnum, "+
	 	"sum(case when vipid in("+vipList+") then 1 else 0 end) vipnum, "+
	 	"sum(case when CustomerStatus=1 then 1 else 0 end) snum "+
		
		" from cqms_wintype_record where branchid like'"+Branchid+"%' ";
		sql_by=" group by left(branchid,(len('"+Branchid+"')+2))";
		if(Branchid.length()==10){
			sql_by=" group by left(branchid,(len('"+Branchid+"')))";
		}
		sql=sql+sql_w+sql_by;
	}
	else if(type.equals("2")){
		sql="select winID,count(1) getnums,"+"sum(case when CustomerStatus=4 then 1 else 0 end) servernum," +
		"sum(case when CustomerStatus=2 then 1 else 0 end) giveupnum, "+
	 	"sum(case when CustomerStatus=0 then 1 else 0 end) waitnum, "+
	 	"sum(case when vipid in("+vipList+") then 1 else 0 end) vipnum, "+
	 	"sum(case when CustomerStatus=1 then 1 else 0 end) snum "+
		" from cqms_wintype_record where branchid like'"+Branchid+"%' and winid is not null ";
		sql_by=" group by winID";
		
		sql=sql+sql_w+sql_by;
	}
	else if(type.equals("3")){
		sql="select username,count(1) getnums,"+"sum(case when CustomerStatus=4 then 1 else 0 end) servernum," +
		"sum(case when CustomerStatus=2 then 1 else 0 end) giveupnum, "+
	 	"sum(case when CustomerStatus=0 then 1 else 0 end) waitnum, "+
	 	"sum(case when vipid in("+vipList+") then 1 else 0 end) vipnum, "+
	 	"sum(case when CustomerStatus=1 then 1 else 0 end) snum "+
		" from cqms_wintype_record r,cqms_operators o where r.branchid like'"+Branchid+"%' and o.branchid=r.branchid  and r.userid=o.loginid";
		sql_by=" group by userid,username";
		
		sql=sql+sql_w+sql_by;
	}
	else if(type.equals("4")){
		sql="select r.wintypeid,w.wintypename,count(1) getnums,"+"sum(case when CustomerStatus=4 then 1 else 0 end) servernum," +
		"sum(case when CustomerStatus=2 then 1 else 0 end) giveupnum, "+
	 	"sum(case when CustomerStatus=0 then 1 else 0 end) waitnum, "+
	 	"sum(case when vipid in("+vipList+") then 1 else 0 end) vipnum, "+
	 	"sum(case when CustomerStatus=1 then 1 else 0 end) snum "+
	 	" from cqms_wintype_record r,cqms_wintypeall w where r.wintypeid=w.wintypeid and branchid" +
	 	" like '"+Branchid+"%' ";
		
		sql_by=" group by r.wintypeid,w.wintypename";
		sql=sql+sql_w+sql_by;
	}
		return sql;
		}
}
